using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

using System.Runtime.InteropServices;

namespace MACL
{
    [ComVisible(true)]
    [Guid("106CD2EB-BF6A-4069-BBBA-880FE5C9CB6C")]
    [ClassInterface(ClassInterfaceType.None)]
    [ProgId("m3Data")]
    public class m3Data : Im3Data
    {
        public ADODB.Recordset ReturnRecordset(String MyConnectionString, String MyTableOrSQL,
                                               ADODB.CursorTypeEnum MyCursorType)
        {
            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rs = new ADODB.Recordset();

            try
            {
                cnn.ConnectionString = MyConnectionString;                
                cnn.Open(null, null, null, 0);
                //}                
                rs.Open(MyTableOrSQL, cnn, MyCursorType, ADODB.LockTypeEnum.adLockOptimistic, -1);
                return rs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public ADODB.Recordset ReturnRecordsetFromFile(String MyConnectionString, String MyTextFilePath,
                                                       ADODB.CursorTypeEnum MyCursorType)
        {
            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rs = new ADODB.Recordset();

            try
            {
                String MyText;

                using (StreamReader fh = new StreamReader(MyTextFilePath))
                {
                    String s;
                    while ((s = fh.ReadLine()) != null)
                        MyText = s;
                    fh.Close();
                    cnn.ConnectionString = MyConnectionString;
                    //if (cnn.State = ConnectionState.Closed)
                    //{
                    cnn.Open(null, null, null, 0);
                    //}                
                    rs.Open(s, cnn, MyCursorType, ADODB.LockTypeEnum.adLockOptimistic, -1);
                }
                return rs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool ExecuteSql(String MyConnectionString, String MySQLStatement)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    cmd.CommandText = MySQLStatement;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }

        public bool ExecuteSqlFromFile(String MyConnectionString, String MyTextFilePath)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    String MyText;
                    using (StreamReader fh = new StreamReader(MyTextFilePath))
                    {
                        string s;
                        while ((s = fh.ReadLine()) != null)
                            MyText = s;
                        fh.Close();
                        cmd.CommandText = s;
                    }
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }

        public bool ExecuteProcedure(String MyConnectionString, String MyProcedureName)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    cmd.CommandText = String.Format("EXEC {0}", MyProcedureName);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }

        public bool ExecuteProcedureFromFile(String MyConnectionString, String MyTextFilePath)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    String MyText;
                    using (StreamReader fh = new StreamReader(MyTextFilePath))
                    {
                        string s;
                        while ((s = fh.ReadLine()) != null)
                            MyText = s;
                        fh.Close();
                        cmd.CommandText = String.Format("EXEC {0}", s);
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }
    }
}
